use crate::duck::connector::get_connection;
use crate::duck::read;
use crate::error::Result;
use crate::schemas::duck::DuckData;
use duckdb::params;

pub fn load_data_by_sql(table_name: &String, create_sql: &String) -> Result<DuckData> {
    // 1. 获取duckDB链接
    let conn = get_connection()?;

    // 2. 先删除表
    conn.execute(
        &format!(r#"DROP TABLE IF EXISTS "{}";"#, table_name),
        params![],
    )?;

    // 3. 创建表并导入 CSV 数据
    conn.execute_batch(create_sql.as_str())?;

    // 4. 查询数据
    let result = read::table_all(table_name)?;

    // 5. 返回数据
    Ok(result)
}

pub fn load_csv(table_name: String, csv_path: String) -> Result<DuckData> {
    let results = load_data_by_sql(
        &table_name,
        &format!(
            r#"CREATE TABLE "{}" AS SELECT * FROM read_csv_auto('{}');"#,
            table_name, csv_path
        ),
    )?;
    Ok(results)
}

pub fn load_excel(table_name: String, file_path: String) -> Result<DuckData> {
    let results = load_data_by_sql(
        &table_name,
        &format!(
            r#"
            INSTALL excel;
            LOAD excel;
            CREATE TABLE "{}" AS SELECT * FROM '{}';
            "#,
            table_name, file_path
        ),
    )?;
    Ok(results)
}

#[cfg(test)]
mod tests {
    use crate::common::variables::update_project_base_dir;
    use crate::duck::loader::{load_csv, load_excel};

    #[test]
    fn test_load_csv() -> Result<(), Box<dyn std::error::Error>> {
        update_project_base_dir("/Users/rich/Workspace/Other/new1".to_string());
        let result = load_csv(
            "csv_example".to_string(),
            "/Users/rich/Workspace/Other/csv/a.csv".to_string(),
        )?;
        print!("csv data: {:?}", result.result);
        Ok(())
    }

    #[test]
    fn test_load_excel() -> Result<(), Box<dyn std::error::Error>> {
        update_project_base_dir("/Users/rich/Workspace/other/new1".to_string());
        let result = load_excel(
            "excel_example".to_string(),
            "/Users/rich/Workspace/other/csv/b.xlsx".to_string(),
        )?;
        print!("excel data: {:?}", result.result);
        Ok(())
    }
}
